package com.cpit.qualitycontrol.dao.jpqlquery;

import com.cpit.qualitycontrol.entity.WorkOrder;
import com.github.pagehelper.StringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import java.util.Iterator;
import java.util.List;

/**
 * Created by xxx on 2017/10/1.
 */
@Component
public class WorkOrderQueryRepository {

    @Autowired
    EntityManagerFactory entityManagerFactory;

    private static String strSqlFromJoin = " FROM WorkOrder w , DeviceType dt , " +
            "Device d , Project p , ProductLine pl " +
            "where w.deviceId = d.id " +
            "and d.deviceTypeId = dt.id " +
            "and d.projectId = p.id " +
            "and d.productLineId = pl.id ";

    public Page<WorkOrder> queryWorkOrder(Long productLineId, String projectName,
                                          String constructionTeamName, String deviceName,
                                          Long deviceTypeId, Integer workOrderState,
                                          List<String> processInstanceIdList, Pageable pageable, String addr) {

        EntityManager entityManager = entityManagerFactory.createEntityManager();


        //查询记录总数
        String strJpqlCountSql = getWorkOrderJpqlCountSqlFixCondition().append(getWorkOrderSqlPartOfCondition(productLineId,
                projectName, constructionTeamName, deviceName, deviceTypeId, workOrderState, processInstanceIdList, addr)).toString();
        Query query = entityManager.createQuery(strJpqlCountSql);
        if (processInstanceIdList != null && processInstanceIdList.size() > 0) {
            query.setParameter("actpiis", processInstanceIdList);
        }
        Long recordCount = (Long) query.getSingleResult();


        String strJpqlSql = getWorkOrderJpqlSqlFixCondition().append(getWorkOrderSqlPartOfCondition(productLineId,
                projectName, constructionTeamName, deviceName, deviceTypeId, workOrderState, processInstanceIdList, addr))
                .append(getSqlPartOfOrderBy(pageable)).toString();
        TypedQuery<WorkOrder> typedQuery = entityManager.createQuery(strJpqlSql, WorkOrder.class);
        if (processInstanceIdList != null && processInstanceIdList.size() > 0) {
            typedQuery.setParameter("actpiis", processInstanceIdList);
        }

        typedQuery.setMaxResults(pageable.getPageSize());   //计数从0开始
        typedQuery.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());

        return new PageImpl<>(typedQuery.getResultList(), pageable, recordCount);
    }


    public WorkOrder queryWorkOrderById(Long workOrderId) {
        EntityManager entityManager = entityManagerFactory.createEntityManager();

        String strJpqlSql = getWorkOrderJpqlSqlFixCondition().append(" and w.workOrderId = " + workOrderId).toString();
        TypedQuery<WorkOrder> typedQuery = entityManager.createQuery(strJpqlSql, WorkOrder.class);

        return typedQuery.getSingleResult();
    }


    /**
     * 根据条件查询工单列表
     *
     * @param productLineId
     * @param projectName
     * @param constructionTeamName
     * @param deviceName
     * @param deviceTypeId
     * @param workOrderState
     * @param processInstanceIdList
     * @return
     */
    public List<WorkOrder> queryWorkOrderByCondition(Long productLineId, String projectName,
                                                     String constructionTeamName, String deviceName,
                                                     Long deviceTypeId, Integer workOrderState,
                                                     List<String> processInstanceIdList) {

        EntityManager entityManager = entityManagerFactory.createEntityManager();

        String strJpqlSql = getWorkOrderJpqlSqlFixCondition().append(getWorkOrderSqlPartOfCondition(productLineId,
                projectName, constructionTeamName, deviceName, deviceTypeId, workOrderState, processInstanceIdList, null)).toString();

        TypedQuery<WorkOrder> typedQuery = entityManager.createQuery(strJpqlSql, WorkOrder.class);
        if (processInstanceIdList != null && processInstanceIdList.size() > 0) {
            typedQuery.setParameter("actpiis", processInstanceIdList);
        }

        return typedQuery.getResultList();
    }


    /**
     * 生成动态可变条件部分字符串
     *
     * @param productLineId
     * @param projectName
     * @param constructionTeamName
     * @param deviceName
     * @param deviceTypeId
     * @param workOrderState
     * @param processInstanceIdList
     * @return
     */
    private StringBuilder getWorkOrderSqlPartOfCondition(Long productLineId, String projectName,
                                                         String constructionTeamName, String deviceName,
                                                         Long deviceTypeId, Integer workOrderState,
                                                         List<String> processInstanceIdList, String addr) {
        StringBuilder sbCondition = new StringBuilder("");
        if (productLineId != null && productLineId != 0) {
            sbCondition.append(" and pl.id = ").append(productLineId);
        }
        if (StringUtil.isNotEmpty(projectName)) {
            sbCondition.append(" and p.name like '%").append(projectName).append("%'");
        }
        if (StringUtil.isNotEmpty(constructionTeamName)) {
            sbCondition.append(" and w.constructionTeamNames like '%").append(constructionTeamName).append("%'");
        }
        if (StringUtil.isNotEmpty(deviceName)) {
            sbCondition.append(" and w.deviceId like '%").append(deviceName).append("%'");
        }
        if (addr != null && !addr.isEmpty()) {
            sbCondition.append(" and d.addrs like '%").append(addr).append("%'");
        }
        if (deviceTypeId != null && deviceTypeId != 0) {
            sbCondition.append(" and d.deviceTypeId = ").append(deviceTypeId);
        }
        if (workOrderState != null) {
            sbCondition.append(" and w.state = ").append(workOrderState);
        }
        if (processInstanceIdList != null && processInstanceIdList.size() != 0) {
            sbCondition.append(" and w.actProcInstId in (:actpiis)");
        }

        return sbCondition;
    }


    /**
     * 生成含固定条件查询记录的JPQL语句
     *
     * @return JPQL语句字符串对象
     */
    private StringBuilder getWorkOrderJpqlSqlFixCondition() {
        return new StringBuilder("SELECT new WorkOrder(w, dt.id, dt.name, p.name, pl.name, d.addrs) " + strSqlFromJoin);
    }


    /**
     * 生成含固定条件查询记录数量的JPQL语句
     *
     * @return JPQL语句字符串对象
     */
    private StringBuilder getWorkOrderJpqlCountSqlFixCondition() {
        return new StringBuilder("SELECT count(w) " + strSqlFromJoin);
    }


    /**
     * 生成JPQL语句中排序部分
     *
     * @param pageable 含排序的分页对象Pageable对象
     * @return 排序部分字符串对象
     */
    private StringBuilder getSqlPartOfOrderBy(Pageable pageable) {
        final String STRING_ORDER_BY = " order by ";
        StringBuilder stringBuilder = new StringBuilder("");

        Sort sort = pageable.getSort();
        if (sort != null) {
            stringBuilder.append(STRING_ORDER_BY);
            Iterator<Sort.Order> iterator = sort.iterator();
            while (iterator.hasNext()) {
                Sort.Order order = iterator.next();
                if (order == null)
                    break;
                String orderProperty = order.getProperty();
                if ("workOrderName".equals(orderProperty))
                    stringBuilder.append("w.workOrderName");
                else if ("deviceId".equals(orderProperty))
                    stringBuilder.append("w.deviceId");
                else if ("deviceTypeId".equals(orderProperty))
                    stringBuilder.append("d.deviceTypeId");
                else if ("projectName".equals(orderProperty))
                    stringBuilder.append("p.projectName");
                else if ("productLineName".equals(orderProperty))
                    stringBuilder.append("pl.productLineName");
                else if ("constructionTeamNames".equals(orderProperty))
                    stringBuilder.append("w.constructionTeamNames");
                else if ("state".equals(orderProperty))
                    stringBuilder.append("w.state");
                else if ("progress".equals(orderProperty))
                    stringBuilder.append("w.progress");
                else if ("assignTime".equals(orderProperty))
                    stringBuilder.append("w.assignTime");
                else if ("endTime".equals(orderProperty))
                    stringBuilder.append("w.endTime");
                else if ("description".equals(orderProperty))
                    stringBuilder.append("w.description");
                else
                    stringBuilder.append("w.").append(orderProperty);
                stringBuilder.append(" ").append(order.getDirection().name()).append(",");
            }
        }
        if (stringBuilder.lastIndexOf(",") > 0)
            stringBuilder.setLength(stringBuilder.length() - 1);
        if (stringBuilder.length() == STRING_ORDER_BY.length())
            stringBuilder.setLength(0);

        return stringBuilder;
    }


}
